package com.sihai.ehr.jdbc;

import java.sql.*;
import java.util.List;

/**
 * author: wxn
 * since: 13-8-7 下午9:58
 */
public class DepartmentService {
    private Connection connection;

    public void connection()throws Exception{
        connection = JDBCHelper.connection();
        if(connection == null){
            throw new IllegalArgumentException("jdbc连接失败");
        }
    }

    public void close()throws Exception{
        if(connection != null && !connection.isClosed()){
            JDBCHelper.close(connection);
        }
    }

    public void initDB(List<Department> departments)throws Exception{
        if(departments != null && !departments.isEmpty()){
            System.out.println("初始化部门start***********************");
            PreparedStatement pst = connection.prepareStatement("insert into dt_dep values(?, ?, ?, ?, ?, ?)");
            for(Department department : departments){
                pst.setInt(1, department.getDep_serial());
                pst.setInt(2, department.getDep_parent());
                pst.setInt(3, department.getDep_order());
                pst.setString(4, department.getDep_name());
                pst.setString(5, department.getDep_no());
                pst.setString(6, department.getDep_rule());
                int row = pst.executeUpdate();
                if(row > 0){
                    System.out.println(String.format("添加成功, 部门名：%s",  department.getDep_name()));
                }else{
                    System.out.println(String.format("添加失败, 部门名：%s",  department.getDep_name()));
                }
            }
            System.out.println("初始化部门end***********************");
        }
    }

    public int insert(Department department)throws Exception{
        if(department != null && department.getDep_serial() > 0){
            //Department parent = null;
            //读父对象
            if(department.getDep_parent() > 0){
                Department parent = findById(department.getDep_parent());
                int count = findChildrenCount(department.getDep_parent());
                department.setDep_no(parent.getDep_no() + "0" + count);
            }
            PreparedStatement pst = connection.prepareStatement("insert into dt_dep values(?, ?, ?, ?, ?, ?)");
            pst.setInt(1, department.getDep_serial());
            pst.setInt(2, department.getDep_parent());
            pst.setInt(3, department.getDep_order());
            pst.setString(4, department.getDep_name());
            pst.setString(5, department.getDep_no());
            pst.setString(6, department.getDep_rule());
            int row = pst.executeUpdate();
            return row;
        }

        return 0;
    }

    public int update(Department department)throws Exception{
        if(department != null){
            PreparedStatement pst = connection.prepareStatement("update dt_dep set dep_name = ? where dep_serial = ?");
            pst.setString(1, department.getDep_name());
            pst.setInt(2, department.getDep_serial());
            int row = pst.executeUpdate();
            return row;
        }

        return 0;
    }

    public int delete(int departmentId)throws Exception{
        if(departmentId > 0){
            PreparedStatement pst = connection.prepareStatement("delete from dt_dep where dep_serial = ?");
            pst.setInt(1, departmentId);
            return pst.executeUpdate();
        }

        return 0;
    }


    public void batchUpdate(List<Department> departments)throws Exception{
        if(departments != null && !departments.isEmpty()){
            System.out.println("start***********************");
            PreparedStatement pst = connection.prepareStatement("update dt_dep set dep_parent=?, dep_order=?, dep_name=?, dep_no=? where dep_serial=?");
            for(Department department : departments){
                pst.setInt(1, department.getDep_parent());
                pst.setInt(2, department.getDep_order());
                pst.setString(3, department.getDep_name());
                pst.setString(4, department.getDep_no());
                pst.setInt(5, department.getDep_serial());
                int row = pst.executeUpdate();
                if(row > 0){
                    System.out.println(String.format("更新成功, 部门名：%s",  department.getDep_name()));
                }else{
                    System.out.println(String.format("更新失败, 部门名：%s",  department.getDep_name()));
                }
            }
            System.out.println("end***********************");
        }
    }

    public Department findById(int id)throws Exception{
        Department department = new Department();
        try{
            PreparedStatement pst = connection.prepareStatement("select * from dt_dep where dep_serial = ?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                department.setDep_order(rs.getInt(1));
                department.setDep_parent(rs.getInt(2));
                department.setDep_order(rs.getInt(3));
                department.setDep_name(rs.getString(4));
                department.setDep_no(rs.getString(5));
                department.setDep_rule(rs.getString(6));
                break;
            }

        }catch(SQLException e) {
            e.printStackTrace();
        }

        return department;
    }

    public int findChildrenCount(int id)throws Exception{
        int count = 0;
        try{
            PreparedStatement pst = connection.prepareStatement("select count(*) from dt_dep where dep_parent = ?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
                break;
            }

        }catch(SQLException e) {
            e.printStackTrace();
        }

        return count;
    }
}
